/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import connection.ConnectionManager;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.EventBean;
import model.SearchBean;
import model.UserBean;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author ozgencetinkaya
 */
public class EventDAO {

    Connection connection;

    public EventDAO() {
        connection = ConnectionManager.getConnection();
    }

    public void insertEvent(EventBean eventBean, UserBean userBean) {


        final String query = "INSERT INTO event (Name,Description,Keyword,Quota,Date,Location) values( '"
                + eventBean.getName() + "','" + eventBean.getDescription() + "','"
                + eventBean.getKeyword() + "','" + eventBean.getQuota() + "','"
                + eventBean.getDateS() + " " + eventBean.getTimeS() + "','"
                + eventBean.getLocation() + "')";



        //   String query2 = "INSERT INTO event (Name,Description,Keyword,Quota,Date) values (?,?,?,?,?)";


        try {
            Statement statement = connection.createStatement();
            statement.execute(query);
            String query3 = "SELECT * FROM event ORDER BY EventID DESC LIMIT 1";
            ResultSet rs = statement.executeQuery(query3);
            rs.next();
            int EventID = rs.getInt("EventID");
            query3 = "SELECT UserID FROM user WHERE Username = '" + userBean.getUsername() + "'";
            rs = statement.executeQuery(query3);
            rs.next();
            int userId = rs.getInt("UserID");
            query3 = "INSERT INTO creatorofevent (EventID,UserID) values ( '"
                    + EventID + "','" + userId + "' )";
            statement.execute(query3);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public List<EventBean> getWillingWalkerEvents(UserBean userBean) {
        try {
            List<EventBean> eventBeans = new ArrayList<EventBean>();

            String query = "SELECT * FROM event e JOIN creatorofevent c ON c.EventID = e.EventID WHERE "
                    + "c.userID = " + userBean.getUserId();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()) {
                EventBean eventBean = new EventBean();
                eventBean.setDate(rs.getDate("Date"));
                eventBean.setDescription(rs.getString("Description"));
                eventBean.setEventID(rs.getInt("EventID"));
                eventBean.setKeyword(rs.getString("Keyword"));
                eventBean.setName(rs.getString("name"));
                eventBean.setQuota(rs.getInt("Quota"));
                eventBeans.add(eventBean);
            }
            return eventBeans;
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<EventBean> searchEvents(SearchBean searchBean) {
        try {
            List<EventBean> eventBeans = new ArrayList<EventBean>();
            String query = "SELECT * FROM event e JOIN creatorofevent c JOIN user u ON c.EventID = e.EventID AND u.UserID = c.UserID WHERE e.Name LIKE '%" + searchBean.getName() + "%' AND "
                    + "e.Keyword LIKE '%" + searchBean.getKeyword() + "%' AND "
                    + "e.Location LIKE '%" + searchBean.getLocation() + "%' AND "
                    + "u.Username LIKE '%" + searchBean.getCreator() + "%'";
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                EventBean eventBean = new EventBean();
                eventBean.setDate(rs.getDate("Date"));
                eventBean.setDescription(rs.getString("Description"));
                eventBean.setEventID(rs.getInt("EventID"));
                eventBean.setKeyword(rs.getString("Keyword"));
                eventBean.setName(rs.getString("name"));
                eventBean.setQuota(rs.getInt("Quota"));
                eventBeans.add(eventBean);
            }
            return eventBeans;
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public void subscribeEvent(int eventId, int userId) {
        try {
            Statement statement = connection.createStatement();
            String query = "INSERT INTO participantofevent (EventID,UserID) values ('"
                    + eventId + "','"
                    + userId + "')";
            statement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getUstreamUserNameOfCreatorOfEvent(int eventId) {
        try {
            String query = "SELECT UstreamUserName FROM user u JOIN creatorofevent c ON u.UserId = c.UserId WHERE c.EventId = " + eventId;

            System.out.println(query);
            Statement statement = connection.createStatement();

            ResultSet rs = statement.executeQuery(query);
            System.out.println(rs);
            rs.next();
            String UstreamUserName = rs.getString("UstreamUserName");


            return UstreamUserName;
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public EventBean getEventData(int eventId) {
        try {
            EventBean eventBean = new EventBean();

            String query = "SELECT * FROM event WHERE EventId=" + eventId;

            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);

            if (rs.next()) {
                //eventBean.setDate(rs.getDate("Date"));
                eventBean.setDescription(rs.getString("Description"));
                eventBean.setEventID(rs.getInt("EventID"));
                eventBean.setKeyword(rs.getString("Keyword"));
                eventBean.setName(rs.getString("name"));
                eventBean.setQuota(rs.getInt("Quota"));

                return eventBean;
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<EventBean> getIsolatedPersonEvents(UserBean userBean) {
        try {
            List<EventBean> eventBeans = new ArrayList<EventBean>();

            String query = "SELECT * FROM event e JOIN participantofevent p ON p.EventID = e.EventID WHERE "
                    + "p.userID = " + userBean.getUserId();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()) {
                EventBean eventBean = new EventBean();
                eventBean.setDate(rs.getDate("Date"));
                eventBean.setDescription(rs.getString("Description"));
                eventBean.setEventID(rs.getInt("EventID"));
                eventBean.setKeyword(rs.getString("Keyword"));
                eventBean.setName(rs.getString("name"));
                eventBean.setQuota(rs.getInt("Quota"));
                eventBeans.add(eventBean);
            }
            return eventBeans;
        } catch (SQLException ex) {
            Logger.getLogger(EventDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public static void main(String[] args) {
        EventDAO eventDAO = new EventDAO();
        System.out.println(eventDAO.getUstreamUserNameOfCreatorOfEvent(14));
        System.out.println(eventDAO.getEventData(14).getName());
    }
}
